﻿/***************************************************COPYRIGHT***************************************
* Copyright (c) 2008, Whiteboard-IT
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without modification, are permitted provided that 
* the following conditions are met:
*
* Redistributions of source code must retain the above copyright notice, this list of conditions and the following 
 * disclaimer.
*
* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following
 * disclaimer in the documentation and/or other materials provided with the distribution.
*
* Neither the name of Whiteboard-IT, LLC nor the names of its contributors may be used to endorse or promote 
 * products derived from this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, 
 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON 
 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
*****************************************************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Boo.Lang;
using MbUnit.Framework;
using Mite.Configuration;
using Mite.MsSql;

namespace Mite.Core.Tests.Integration
{
	[Author("Nathan")]
	[TestCategory("MsSql Integration")]
	[TestFixture]
	public class MsSqlAdapterTestFixture : MsSqlIntegrationTestBase
	{
		[SetUp]
		public void SetUp()
		{
			Environment.CurrentDirectory = AppDomain.CurrentDomain.BaseDirectory;
			using (var conn = IoC.Resolve<IDbConnection>())
			{
				conn.Open();
				MsSqlUtility.CleanDatabase(conn, testDatabase.Name);
				conn.Close();
			}
		}

		[Test]
		public void CreateTable()
		{
			const string tableName = "some_table";
			using (IDbConnection conn = GetConnection())
			{
				conn.Open();

				MsSqlAdapter dao = GetMsSqlAdapter();

				IDbCommand cmd = dao.CreateTable(conn, new Table(tableName, GetIdColumn()));
				cmd.ExecuteNonQuery();
			}

			AssertTableExists(tableName);
		}

		[Test]
		public void DropTable()
		{
			const string tableName = "some_table";

			var table = new Table(tableName, GetIdColumn());

			MsSqlAdapter adapter = GetMsSqlAdapter();

			using (IDbConnection conn = GetConnection(adapter))
			{
				conn.Open();

				IDbCommand cmd = adapter.CreateTable(conn, table);
				cmd.ExecuteNonQuery();

				AssertTableExists(tableName);

				IDbCommand dropCommand = adapter.DropTable(conn, table);
				dropCommand.ExecuteNonQuery();

				AssertTableDoesNotExist(tableName);

				conn.Close();
			}
		}

		[Test]
		public void CreateSchemaTable()
		{
			using (IDbConnection conn = GetConnection())
			{
				conn.Open();
				MsSqlAdapter dao = GetMsSqlAdapter();

				IDbCommand[] cmds = dao.CreateSchemaTable(conn, testDatabase);
				cmds.ForEach(cmd => { cmd.ExecuteNonQuery(); });
			}

			AssertTableExists(Adapter.MIGRATION_SCHEMA_TABLE_NAME);
		}

		[Test]
		public void RenameTable()
		{
			const string newName = "new_name";
			Table table = GetTable();
			string originalName = table.Name;

			MsSqlAdapter adapter = GetMsSqlAdapter();

			using (IDbConnection conn = GetConnection(adapter))
			{
				conn.Open();

				IDbCommand createCommand = adapter.CreateTable(conn, table);
				createCommand.ExecuteNonQuery();

				AssertTableExists(originalName);

				IDbCommand renameCommand = adapter.RenameTable(conn, table, newName);
				renameCommand.ExecuteNonQuery();

				AssertTableDoesNotExist(originalName);
				AssertTableExists(newName);

				conn.Close();
			}
		}

		[Test]
		public void AlterColumn()
		{
			const string columnName = "some_column";

			Column originalColumn = new NVarCharColumn(columnName, 200);
			Column newColumn = new NVarCharColumn(columnName, 255)
							   {
								   IsNullable = true
							   };

			Table table = GetTable(originalColumn);
			newColumn.Table = table;

			MsSqlAdapter adapter = GetMsSqlAdapter();

			using (IDbConnection conn = GetConnection(adapter))
			{
				conn.Open();

				IDbCommand createCommand = adapter.CreateTable(conn, table);

				createCommand.ExecuteNonQuery();

				IDbCommand alterCommand = adapter.AlterColumn(conn, originalColumn, newColumn);
				alterCommand.ExecuteNonQuery();

				conn.Close();
			}
		}

		[Test]
		public void AlterTable_AddColumn()
		{
			CreateAlterMeTable();

			var addedColumn = new Column("added_column", DbType.Decimal);
			addedColumn.Table = new Table("alter_me", addedColumn);

			// TODO: This shouldn't be checked by the dao, but right now this is how it's done.
			MsSqlAdapter dao = GetMsSqlAdapter();
			using (IDbConnection conn = GetConnection())
			{
				conn.Open();

				IDbCommand cmd = dao.AddColumnToTable(conn, new Table("alter_me"), addedColumn);
				cmd.ExecuteNonQuery();

				Database db = dao.GetDatabase(conn, testDatabase.Name);
				Table tbl = db.Tables.Single(t => t.Name.Equals("alter_me"));

				Assert.IsTrue(tbl.Columns.Contains(addedColumn));
			}
		}

		[Test]
		public void RemoveColumn()
		{
			CreateAlterMeTable();

			MsSqlAdapter dao = GetMsSqlAdapter();
			using (IDbConnection conn = GetConnection())
			{
				conn.Open();

				IDbCommand cmd = dao.DropColumns(conn, "alter_me", "remove_me");
				cmd.ExecuteNonQuery();

				Database db = dao.GetDatabase(conn, testDatabase.Name);
				Table tbl = db.Tables.Single(t => t.Name.Equals("alter_me"));

				Assert.IsTrue(tbl.Columns.DoesNotContain(col => col.Name == "remove_me"));
				conn.Close();
			}
		}

		[Test]
		public void AlterColumn_ChangeName()
		{
			string newColumnName = "new_column_name";

			using (var conn = IoC.Resolve<IDbConnection>() as SqlConnection)
			{
				conn.Open();
				MsSqlAdapter adapter = GetMsSqlAdapter();

				Column idColumn = GetIdColumn();
				Column oldColumn = new Column("old_column", DbType.String);

				Table table = new Table("alter_test", idColumn, oldColumn);

				using (IDbCommand cmd = adapter.CreateTable(conn, table))
					cmd.ExecuteNonQuery();

				using (IDbCommand cmd = adapter.RenameColumn(conn, oldColumn, newColumnName))
					cmd.ExecuteNonQuery();

				Database db = adapter.GetDatabase(conn, testDatabase.Name);
				var updatedTable = db.Tables.Single(tbl => tbl.Equals(table));
				var updatedColumn = updatedTable.Columns.SingleOrDefault(col => col.Name.Equals(newColumnName));

				Assert.IsNotNull(updatedColumn, "Column rename failed.");

				conn.Close();
			}
		}

		[Test]
		public void AlterColumn_ChangeType()
		{
			const string decimalColumnName = "datatype_col_decimal";
			const string tableName = "alter_test";

			using (IDbConnection conn = GetConnection())
			{
				conn.Open();

				MsSqlAdapter dao = GetMsSqlAdapter();

				Column idColumn = GetIdColumn();
				Column decimalColumn = new Column(decimalColumnName, DbType.Decimal);

				Table table = new Table(tableName, idColumn, decimalColumn);

				using (IDbCommand cmd = dao.CreateTable(conn, table))
					cmd.ExecuteNonQuery();
				
				var modifiedColumn = decimalColumn.CloneColumn();
				modifiedColumn.DataType = DbType.StringFixedLength;
				modifiedColumn.Length = 255;

				using (IDbCommand cmd = dao.AlterColumn(conn, decimalColumn, modifiedColumn))
					cmd.ExecuteNonQuery();

				Assert.IsTrue(MsSqlUtility.ColumnIsOfDataType(conn, testDatabase.Name, tableName, decimalColumnName, "nchar"),
							  "Changing datatype of column failed.");

				conn.Close();
			}
		}

		[Test]
		public void AddForeignKey()
		{
			MsSqlAdapter adapter = GetAdapter(environment);

			using (var conn = environment.CreateConnection(adapter))
			{
				conn.Open();

				string pkTableName = "pk_table";
				string fkTableName = "fk_table";

				Table table = new Table(pkTableName,
				                        new Column("id", DbType.Int32, ColumnAttributes.PrimaryKey | ColumnAttributes.Identity),
										new Column("fk_tableId", DbType.Int32, ColumnAttributes.NotNull));

				Table referenceTable = new Table(fkTableName,
					new Column("Id", DbType.Int32, ColumnAttributes.PrimaryKey | ColumnAttributes.Identity));

				using (IDbTransaction trans = conn.BeginTransaction())
				{
					using (IDbCommand cmd = adapter.CreateTable(conn, table))
					{
						cmd.Transaction = trans;
						cmd.ExecuteNonQuery();
					}

					using (IDbCommand cmd = adapter.CreateTable(conn, referenceTable))
					{
						cmd.Transaction = trans;
						cmd.ExecuteNonQuery();
					}

					trans.Commit();
				}

				using (IDbCommand cmd = adapter.AddForeignKey(conn, pkTableName, fkTableName, "FK_BLEH_BLAH", new KeyValuePair<string, string>("fk_tableId", "Id")))
					cmd.ExecuteNonQuery();

				Assert.IsTrue(MsSqlUtility.ForeignKeyExists(conn, testDatabase.Name, "FK_BLEH_BLAH"), "Foreign Key not added.");

				conn.Close();
			}
		}

		[Test]
		public void AlterTable_RemoveForeignKey()
		{
			const string pkTableName = "pk_table";
			const string fkTableName = "fk_table";
			const string fkColumnName = "foreign_key_test";
			const string fkName = "FK_BLEH_BLUR";

			using (var conn = IoC.Resolve<IDbConnection>())
			{
				conn.Open();

				using (IDbTransaction trans = conn.BeginTransaction())
				{
					using (IDbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = String.Format("CREATE TABLE {0} ([id] int primary key identity not null);", pkTableName);
						cmd.Transaction = trans;
						cmd.ExecuteNonQuery();
					}

					using (IDbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = String.Format("CREATE TABLE {0} ([id] int primary key identity not null, [{1}] int not null);",
														fkTableName, fkColumnName);
						cmd.Transaction = trans;
						cmd.ExecuteNonQuery();
					}

					using (IDbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = String.Format("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3} ({4});",
														fkTableName, fkName, fkColumnName, pkTableName, "id");
						cmd.Transaction = trans;
						cmd.ExecuteNonQuery();
					}

					trans.Commit();
				}

				MsSqlAdapter dao = GetMsSqlAdapter();
				using (IDbCommand cmd = dao.DropForeignKey(conn, fkTableName, fkName))
					cmd.ExecuteNonQuery();


				Assert.IsFalse(MsSqlUtility.ForeignKeyExists(conn, testDatabase.Name, fkName));
				conn.Close();
			}
		}

		[Test]
		public void Create_unique_index_on_two_columns()
		{
			string colToIndexName = "col_to_index";
			string tableName = "table_name";
			using (var conn = IoC.Resolve<IDbConnection>())
			{
				conn.Open();

				using (IDbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = string.Format("CREATE TABLE {0} ([id] int primary key not null, [{1}] nvarchar(200))", tableName,
													colToIndexName);
					cmd.ExecuteNonQuery();
				}


				MsSqlAdapter adapter = GetMsSqlAdapter();
				var index = new Index("IX_Test", new Table(tableName), true, new Hash(),
									  GetIdColumn(),
									  new MigrationBase.String(colToIndexName) { Length = 200});

				IDbCommand createIndexCommand = adapter.CreateIndex(index, conn);

				Console.WriteLine(createIndexCommand.CommandText);

				createIndexCommand.ExecuteNonQuery();

				conn.Close();
			}
		}

		private MsSqlAdapter GetAdapter(MiteEnvironment environment)
		{
			return GetMsSqlAdapter();
		}

		/// <summary>
		/// Create a table with name "test" and an id int32 identity column.
		/// </summary>
		private static Table GetTable(params Column[] columns)
		{
			var columnList = new List<Column>();
			columnList.Add(GetIdColumn());
			columnList.AddRange(columns);
			return new Table("test", columnList.ToArray());
		}

		private void CreateAlterMeTable()
		{
			using (var conn = IoC.Resolve<IDbConnection>())
			{
				conn.Open();

				conn.ChangeDatabase(testDatabase.Name);

				using (IDbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText =
						String.Format("CREATE TABLE [alter_me] ([id] int not null identity primary key, [remove_me] nchar(2) null)");
					cmd.ExecuteNonQuery();
				}

				conn.Close();
			}
		}

		protected MsSqlAdapter GetMsSqlAdapter()
		{
			var adapter = new MsSqlAdapter();
			return adapter;
		}

		protected IDbConnection GetConnection()
		{
			var adapter = new MsSqlAdapter();
			IDbConnection conn = adapter.CreateConnection(
				environment.Host,
				environment.Database,
				environment.Username,
				environment.Password,
				environment.Options);
			return conn;
		}

		protected IDbConnection GetConnection(Adapter adapter)
		{
			return adapter.CreateConnection(environment.Host, environment.Database, environment.Username, environment.Password,
											environment.Options);
		}

		private static Column GetIdColumn()
		{
			return new Column("Id", DbType.Int32, ColumnAttributes.Identity | ColumnAttributes.PrimaryKey);
		}

		private static void AssertTableExists(string tableName)
		{
			using (var conn = IoC.Resolve<IDbConnection>() as SqlConnection)
			{
				conn.Open();


				IEnumerable<string> tableNames = MsSqlUtility.GetTableNamesInDatabase(DATABASE_NAME, conn);
				Assert.IsTrue(tableNames.Contains(tableName));


				conn.Close();
			}
		}

		private static void AssertTableDoesNotExist(string tableName)
		{
			using (var conn = IoC.Resolve<IDbConnection>())
			{
				conn.Open();


				IEnumerable<string> tableNames = MsSqlUtility.GetTableNamesInDatabase(DATABASE_NAME, conn);
				Assert.IsFalse(tableNames.Contains(tableName));
			}
		}

		private readonly Database testDatabase = new Database(DATABASE_NAME);
	}
}